import time

import pymysql
from pymysql import Connection, DatabaseError, Error

from common.time_util import time_to_string
from common.yml_util import read_yml


class DbUtil:



    def db_exec_select(self, host_, user_, password_, port_, db_, sql_):
        """
        sql查询
        Args:
            host_:
            user_:
            password_:
            port_:
            db_:
            sql_:

        Returns:

        """
        conn = ""
        try:
            # 获取connect连接对象
            conn = pymysql.connect(host=host_, user=user_, password=password_, port=port_, db=db_)
            # 获取游标
            cur = conn.cursor()
            # 执行sql语句
            effect_rows = cur.execute(sql_)
            print(f"cursor execute result={effect_rows}")
            # 获取数据集
            stu_list = cur.fetchall()
            # print(type(results))
            print(f"stu_list={stu_list}")
        except Error as be:
            print(f"=======连接异常========:{be.args}")
        else:
            print("======db连接成功==========")
        finally:
            conn.close()

    def do_exec_update(self, host_, user_, password_, port_, db_, sql_):
        """
        sql 更新、删除操作
        :param host_:
        :param user_:
        :param password_:
        :param port_:
        :param db_:
        :param sql_:
        :return: 受影响行数
        """
        db = ""
        try:
            db = pymysql.connect(host=host_, user=user_, password=password_, port=port_, db=db_)
            cur = db.cursor()
            r = cur.execute(sql_)
            print(f"effect_rows={r}")
            db.commit()
        except Error as be:
            print(f"=======连接异常========:{be.args}")
            db.rollback()
        else:
            print("======db连接成功==========")
        finally:
            db.close()


if __name__ == '__main__':
    DB_CONFIG = {
        "host": "localhost",
        "user": "root",
        "pwd": "root8.1",
        "port": 3306,
        "db": "train-sql"
    }

    sql = "SELECT * from student"
    DbUtil().db_exec_select(DB_CONFIG['host'], DB_CONFIG['user'], DB_CONFIG['pwd'], DB_CONFIG['port'], DB_CONFIG['db'], sql)

    sql_update = "update student set s_birth='%s' where s_id=%d" % ('1995-09-19', 4)

    sql_insert = "insert into teacher (t_name, create_time)values ('%s', '%s')" % ('小明', time_to_string(format_=None))

    sql_delete = "delete from teacher where t_id=%s" %(5)

    DbUtil().do_exec_update(DB_CONFIG['host'],
                            DB_CONFIG['user'],
                            DB_CONFIG['pwd'],
                            DB_CONFIG['port'],
                            DB_CONFIG['db'],
                            sql_delete)
